package db_layer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import model_layer.Product;
import model_layer.Sale;
import model_layer.SaleLineItem;

public class SaleDB
{
	private Connection con;
	public SaleDB()
	{
		con = DBConnection.get_instance().get_connection();
	}
	
	public Sale find_sale(String var_name, int var, boolean make_association)
	{
		return single_find(var_name, "", var, make_association);
	}
	
	public Sale find_sale(String var_name, String var, boolean make_association)
	{
		return single_find(var_name, var, -1, make_association);
	}
	
	public List<Sale> find_all_sales(boolean make_association)
	{
		return multiple_find("", "", -1, make_association);
	}
	
	public List<Sale> find_particular_sales(String var_name, int var, boolean make_association)
	{
		return multiple_find(var_name, "", var, make_association);
	}
	
	public int insert_sale(Sale sale)
	{
		int return_code = -1;
		int current_account_id = sale.getStaff_member().getId();
		int cust_id = Integer.parseInt(sale.getCustomer().getId());
		List<SaleLineItem> items = sale.getItems();
		Date sale_date = sale.getSale_date();
		float special_discount = sale.getSpecial_discount();
		float total_price = sale.getTotal_price();
		try
		{
			DBConnection.begin_transaction();
			ProductDB prod_db = new ProductDB();
			if(!prod_db.check_if_quantities_are_high_enough(items))	//Here we perform final check if all amounts are still not above current quantity
			{									//If the insert would make the amounts illegally drop below zero, then we return a return code -2
				System.out.println("Quantities not high enough, rolling back the transaction.");//Why -2 you ask? Because -1 is already taken for normal error in the database
				DBConnection.rollback_transaction();		//Also if we would go below allowed quantity, we rollback the transaction and make no changes in the db.
				return -2;
			}
			PreparedStatement sale_ps = DBUtilities.make_insert_statement(con, "Sales", "sale_date, special_discount, total_price, cust_id, sm_id");
			sale_ps.setDate(1, new java.sql.Date(sale_date.getTime()));
			sale_ps.setFloat(2, special_discount);
			sale_ps.setFloat(3, total_price);
			sale_ps.setInt(4, cust_id);
			sale_ps.setInt(5, current_account_id);
			sale_ps.setQueryTimeout(5);
			sale_ps.executeUpdate();
			sale_ps.close();
			int id = DBUtilities.get_max(con, "Sales", "id");
			insert_items(items, id);
			prod_db.subtract_amounts(items);
			DBConnection.end_transaction();
			return_code = 1;
		}
		catch(SQLException e)
		{
			System.err.println("Error while inserting sale: " + e);
			DBConnection.rollback_transaction();
		}
		return return_code;
	}
	
	public boolean delete_sale(int sale_id)
	{
		boolean success = false;
		try
		{
			PreparedStatement sale_ps = DBUtilities.make_delete_statement(con, "Sales", "id");
			sale_ps.setInt(1, sale_id);
			sale_ps.setQueryTimeout(5);
			sale_ps.executeUpdate();
			success = true;
			
		}
		catch(SQLException e)
		{
			System.err.println("Error while deleting a sale: " + e);
		}
		return success;
	}
	
	private void insert_items(List<SaleLineItem> items, int sale_id) throws SQLException
	{
		int number_of_items = items.size();
		PreparedStatement sale_line_item_ps = DBUtilities.make_multiple_insert_statement(con, "SaleLineItems", "price, sale_id, prod_id, amount", number_of_items);
		int n = 0;
		for (SaleLineItem item : items)
		{
			sale_line_item_ps.setFloat(1 + 4 * n, item.getPrice());
			sale_line_item_ps.setInt(2 + 4 * n, sale_id);
			sale_line_item_ps.setInt(3 + 4 * n, Integer.parseInt(item.getProduct().getId()));
			sale_line_item_ps.setInt(4 + 4 * n, item.getAmount());
			n++;
		}
		
		sale_line_item_ps.setQueryTimeout(5);
		sale_line_item_ps.executeUpdate();
		sale_line_item_ps.close();

	}
	
	private Sale single_find(String var_name, String s_var, int i_var, boolean make_association)
	{
		String query = make_sale_query();
		ResultSet results;
		Sale sale = null;
		try
		{
			DBConnection.begin_transaction();
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, var_name, s_var, i_var);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			if(results.next())
			{
				sale = create_sale(results);
				
				if(make_association)
				{
					query = make_items_query();
					PreparedStatement ps_i = DBUtilities.prepare_statement(con, query, "sale_id", sale.getId());
					ps_i.setQueryTimeout(5);
					results = ps_i.executeQuery();
					List<SaleLineItem> items = create_item_array(results);
					sale.setItems(items);
					ps_i.close();
				}
			}
			ps.close();
			DBConnection.end_transaction();
		}
		catch(SQLException e)
		{
			System.out.println("Error while selecting a sale: " + e);
			DBConnection.rollback_transaction();
		}
		return sale;	
	}
	
	private List<Sale> multiple_find(String var_name, String s_var, int i_var, boolean make_association)
	{
		String query = make_sale_query();
		ResultSet results;
		ResultSet item_results;
		Sale sale = null;
		List<Sale> sales = new ArrayList<Sale>();
		try
		{
			DBConnection.begin_transaction();
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, var_name, s_var, i_var);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			while(results.next())
			{
				sale = create_sale(results);
				if(make_association)
				{
					query = make_items_query();
					PreparedStatement item_ps = DBUtilities.prepare_statement(con, query, "sale_id", sale.getId());
					item_ps.setQueryTimeout(5);
					item_results = item_ps.executeQuery();
					List<SaleLineItem> items = create_item_array(item_results);
					sale.setItems(items);
					item_ps.close();
				}
				sales.add(sale);
			}
			ps.close();
			DBConnection.end_transaction();
			
		}
		catch(SQLException e)
		{
			sales.clear();
			System.out.println("Error while selecting sales: " + e);
			DBConnection.rollback_transaction();
		}
		return sales;	
	}
	
	private Sale create_sale(ResultSet results)
	{
		try
		{
			Sale sale = new Sale();
			sale.setId(results.getInt("id"));
			Date sale_date = new Date(results.getDate("sale_date").getTime());
			CustomerDB cust_db = new CustomerDB();
			sale.setCustomer(cust_db.find_customer("id", results.getInt("cust_id")));
			sale.setSale_date(sale_date);
			sale.setSpecial_discount(results.getFloat("special_discount"));
			sale.setTotal_price(results.getFloat("total_price"));
			StaffMemberDB staff_db = new StaffMemberDB();
			sale.setStaff_member(staff_db.find_staff_member("id", results.getInt("sm_id"), false));
			return sale;
		}
		catch(SQLException e)
		{
			System.out.println("Error while creating a sale: " + e);
			return null;
		}		
	}
	
	private List<SaleLineItem> create_item_array(ResultSet results)
	{
		List<SaleLineItem> items = new ArrayList<SaleLineItem>();
		ProductDB prod_db = new ProductDB();
		try
		{
			while(results.next())
			{
				SaleLineItem item = new SaleLineItem();
				item.setAmount(results.getInt("amount"));
				item.setPrice(results.getFloat("price"));
				Product prod = prod_db.find_product("id", results.getInt("prod_id"));
				if(prod == null)
				{
					SQLException e = new SQLException();
					throw e;
				}
				item.setProduct(prod);
				items.add(item);
			}
		}
		catch(SQLException e)
		{
			System.out.println("Error while creating items array: " + e);
			items.clear();
		}
		return items;
	}
	
	private String make_sale_query()
	{
		return DBUtilities.make_query("id, sale_date, special_discount, total_price, cust_id, sm_id", "Sales");
	}
	
	private String make_items_query()
	{
		return DBUtilities.make_query("id, price, prod_id,amount", "SaleLineItems");
	}
	

}
